//    Travel US - For users of travel.usairways.com
//    intended for US Airways employees and their dependents
//    This app will help you track your flights and display
//    Availability, flight status and other non-rev info
//    Copyright (C) 2014  Renats Stozkovs
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>.

package com.bc004346.travelus.DataAccess;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.bc004346.travelus.Model.Domain.Flight;
import com.bc004346.travelus.Model.Domain.Passenger;
import com.bc004346.travelus.Model.Domain.Reservation;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

import static com.bc004346.travelus.Model.SQL.FlightContract.FlightEntry;
import static com.bc004346.travelus.Model.SQL.PassengerContract.PassengerEntry;
import static com.bc004346.travelus.Model.SQL.ReservationContract.ReservationEntry;
import static com.bc004346.travelus.Utility.log;

/**
 * Created by Renats on 1/19/14.
 * SQL Lite helper
 */
public class SQLHelper extends SQLiteOpenHelper {

    //<editor-fold desc="General Database section">

    private static final String TEXT_TYPE = " TEXT";
    private static final String INTEGER_TYPE = " INTEGER";
    private static final String COMMA_SEP = ",";

    public static final int DATABASE_VERSION = 10;
    public static final String DATABASE_NAME = "TravelUS.db";

    public SQLHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    private static final String SQL_CREATE_RESERVATION_ENTRIES =
            "CREATE TABLE IF NOT EXISTS " + ReservationEntry.TABLE_NAME + " (" +
                    ReservationEntry.COLUMN_NAME_PNR + TEXT_TYPE + " PRIMARY KEY" + COMMA_SEP +
                    ReservationEntry.COLUMN_NAME_DEPARTURE_DATETIME + INTEGER_TYPE + COMMA_SEP +
                    ReservationEntry.COLUMN_NAME_BOOKING_TYPE + TEXT_TYPE +
                    " )";

    private static final String SQL_DELETE_RESERVATION_ENTRIES =
            "DROP TABLE IF EXISTS " + ReservationEntry.TABLE_NAME;


    private static final String SQL_CREATE_FLIGHT_ENTRIES =
            "CREATE TABLE IF NOT EXISTS " + FlightEntry.TABLE_NAME + " (" +
                    FlightEntry.COLUMN_NAME_PNR + TEXT_TYPE +
                    " REFERENCES " + ReservationEntry.TABLE_NAME + "(" +
                    ReservationEntry.COLUMN_NAME_PNR + ")" + COMMA_SEP +
                    FlightEntry.COLUMN_NAME_DEPARTURE_DATETIME + INTEGER_TYPE + COMMA_SEP +
                    FlightEntry.COLUMN_NAME_ARRIVAL_DATETIME + INTEGER_TYPE + COMMA_SEP +
                    FlightEntry.COLUMN_NAME_FLIGHT_NUMBER + TEXT_TYPE + COMMA_SEP +
                    FlightEntry.COLUMN_NAME_ORIGIN + TEXT_TYPE + COMMA_SEP +
                    FlightEntry.COLUMN_NAME_DESTINATION + TEXT_TYPE + COMMA_SEP +
                    FlightEntry.COLUMN_NAME_IS_CHECKED_IN + TEXT_TYPE + COMMA_SEP +
                    FlightEntry.COLUMN_NAME_IS_CHECK_IN_SCHEDULED + TEXT_TYPE +
                    " )";

    private static final String SQL_DELETE_FLIGHT_ENTRIES =
            "DROP TABLE IF EXISTS " + FlightEntry.TABLE_NAME;

    private static final String SQL_CREATE_PASSENGER_ENTRIES =
            "CREATE TABLE IF NOT EXISTS " + PassengerEntry.TABLE_NAME + " (" +
                    PassengerEntry.COLUMN_NAME_PNR + TEXT_TYPE +
                    " REFERENCES " + ReservationEntry.TABLE_NAME + "(" +
                    ReservationEntry.COLUMN_NAME_PNR + ")" + COMMA_SEP +
                    PassengerEntry.COLUMN_NAME_NAME + TEXT_TYPE + COMMA_SEP +
                    PassengerEntry.COLUMN_NAME_RELATIONSHIP + TEXT_TYPE + COMMA_SEP +
                    PassengerEntry.COLUMN_NAME_TICKET_NUMBER + TEXT_TYPE +
                    " )";

    private static final String SQL_DELETE_PASSENGER_ENTRIES =
            "DROP TABLE IF EXISTS " + PassengerEntry.TABLE_NAME;

    @Override
    public void onCreate(SQLiteDatabase db) {
        log("starting...");

        db.execSQL(SQL_CREATE_RESERVATION_ENTRIES);
        db.execSQL(SQL_CREATE_FLIGHT_ENTRIES);
        db.execSQL(SQL_CREATE_PASSENGER_ENTRIES);

        log("end...");
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        log(String.format("starting... oldVersion = %d, newVersion = %d", oldVersion, newVersion));

        onUpgrade(db, oldVersion, newVersion);

        log("end...");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        log(String.format("starting... oldVersion = %d, newVersion = %d", oldVersion, newVersion));

        db.execSQL(SQL_DELETE_PASSENGER_ENTRIES);
        db.execSQL(SQL_DELETE_FLIGHT_ENTRIES);
        db.execSQL(SQL_DELETE_RESERVATION_ENTRIES);
        onCreate(db);

        log("end...");
    }

    //</editor-fold>

    //<editor-fold desc="Data Selection">

    public List<Reservation> SelectReservations() {
        log("starting...");

        SQLiteDatabase db = getReadableDatabase();
        List<Reservation> reservations = new ArrayList<Reservation>();

        Cursor pnrCursor;
        Cursor paxCursor;

        String[] pnrProjection = {
                ReservationEntry.COLUMN_NAME_PNR,
                ReservationEntry.COLUMN_NAME_DEPARTURE_DATETIME,
                ReservationEntry.COLUMN_NAME_BOOKING_TYPE};

        String[] paxProjection = {
                PassengerEntry.COLUMN_NAME_NAME,
                PassengerEntry.COLUMN_NAME_RELATIONSHIP,
                PassengerEntry.COLUMN_NAME_TICKET_NUMBER};

        String paxSelection = PassengerEntry.COLUMN_NAME_PNR + " = ?";

        if (db != null) {

            pnrCursor = db.query(ReservationEntry.TABLE_NAME, pnrProjection, null, null, null, null, null);

            if (pnrCursor != null && pnrCursor.getCount() > 0) {
                pnrCursor.moveToFirst();
                do {
                    String pnr = pnrCursor.getString(pnrCursor.getColumnIndexOrThrow(ReservationEntry.COLUMN_NAME_PNR));
                    Date date = new Date(pnrCursor.getLong(pnrCursor.getColumnIndexOrThrow(ReservationEntry.COLUMN_NAME_DEPARTURE_DATETIME)));
                    String bookingType = pnrCursor.getString(pnrCursor.getColumnIndexOrThrow(ReservationEntry.COLUMN_NAME_BOOKING_TYPE));
                    Reservation reservation = new Reservation(pnr, date, bookingType);

                    reservation.setFlights(SelectFlights(pnr));

                    paxCursor = db.query(PassengerEntry.TABLE_NAME, paxProjection, paxSelection, new String[]{pnr}, null, null, null);
                    if (paxCursor != null && paxCursor.getCount() > 0) {
                        List<Passenger> passengers = new ArrayList<Passenger>();
                        paxCursor.moveToFirst();
                        do {
                            String name = paxCursor.getString(paxCursor.getColumnIndexOrThrow(PassengerEntry.COLUMN_NAME_NAME));
                            String relationship = paxCursor.getString(paxCursor.getColumnIndexOrThrow(PassengerEntry.COLUMN_NAME_RELATIONSHIP));
                            String ticketNumber = paxCursor.getString(paxCursor.getColumnIndexOrThrow(PassengerEntry.COLUMN_NAME_TICKET_NUMBER));
                            passengers.add(new Passenger(name, relationship, ticketNumber));
                        } while (paxCursor.moveToNext());
                        reservation.setPassengers(passengers);
                    }

                    reservations.add(reservation);

                } while (pnrCursor.moveToNext());
            }
        }

        log(String.format("end... returning %d reservation(s)", reservations.size()));

        return reservations;
    }

    public List<Flight> SelectFlights(String pnr) {

        log("starting...");

        List<Flight> flights = new ArrayList<Flight>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor flightCursor;

        String[] flightProjection = {
                FlightEntry.COLUMN_NAME_ARRIVAL_DATETIME,
                FlightEntry.COLUMN_NAME_DEPARTURE_DATETIME,
                FlightEntry.COLUMN_NAME_DESTINATION,
                FlightEntry.COLUMN_NAME_FLIGHT_NUMBER,
                FlightEntry.COLUMN_NAME_IS_CHECKED_IN,
                FlightEntry.COLUMN_NAME_IS_CHECK_IN_SCHEDULED,
                FlightEntry.COLUMN_NAME_ORIGIN};

        String flightSelection = FlightEntry.COLUMN_NAME_PNR + " = ?";
        if (db != null) {
            flightCursor = db.query(FlightEntry.TABLE_NAME, flightProjection, flightSelection, new String[]{pnr}, null, null, null);
            if (flightCursor != null && flightCursor.getCount() > 0) {
                flightCursor.moveToFirst();
                do {
                    String origin = flightCursor.getString(flightCursor.getColumnIndexOrThrow(FlightEntry.COLUMN_NAME_ORIGIN));
                    String destination = flightCursor.getString(flightCursor.getColumnIndexOrThrow(FlightEntry.COLUMN_NAME_DESTINATION));
                    Date departure = new Date(flightCursor.getLong(flightCursor.getColumnIndexOrThrow(FlightEntry.COLUMN_NAME_DEPARTURE_DATETIME)));
                    Date arrival = new Date(flightCursor.getLong(flightCursor.getColumnIndexOrThrow(FlightEntry.COLUMN_NAME_ARRIVAL_DATETIME)));
                    Integer flightNumber = flightCursor.getInt(flightCursor.getColumnIndexOrThrow(FlightEntry.COLUMN_NAME_FLIGHT_NUMBER));
                    Boolean isCheckedIn = Boolean.valueOf(flightCursor.getString(flightCursor.getColumnIndexOrThrow(FlightEntry.COLUMN_NAME_IS_CHECKED_IN)));
                    Boolean isCheckInScheduled = Boolean.valueOf(flightCursor.getString(flightCursor.getColumnIndexOrThrow(FlightEntry.COLUMN_NAME_IS_CHECK_IN_SCHEDULED)));

                    Flight flight = new Flight(flightNumber, departure, origin, destination, arrival, isCheckedIn, pnr, isCheckInScheduled);
                    flights.add(flight);
                    log(String.format("Adding flight %s, isCheckedIn = %s, isCheckInScheduled = %s",
                            flight, flight.isCheckedIn(), flight.isCheckInScheduled()));
                } while (flightCursor.moveToNext());
            }
        }

        log(String.format("end... returning %d flights", flights.size()));

        return flights;
    }

    //</editor-fold>

    //<editor-fold desc="Data Insertion">

    public void InsertReservations(List<Reservation> reservations) {
        log(String.format("starting... %d reservation(s)", reservations.size()));

        SQLiteDatabase db = getWritableDatabase();
        long insertedRes = 0;

        if (db != null) {
            ContentValues values = new ContentValues();
            for (Reservation res : reservations) {
                values.clear();
                values.put(ReservationEntry.COLUMN_NAME_PNR, res.getPnr());
                values.put(ReservationEntry.COLUMN_NAME_DEPARTURE_DATETIME, res.getDeparture().getTime());
                values.put(ReservationEntry.COLUMN_NAME_BOOKING_TYPE, res.getBookingType());

                insertedRes += db.insertOrThrow(ReservationEntry.TABLE_NAME, "", values);

                InsertFlights(res.getFlights());

                for (Passenger pax : res.getPassengers()) {
                    values.clear();
                    values.put(PassengerEntry.COLUMN_NAME_PNR, res.getPnr());
                    values.put(PassengerEntry.COLUMN_NAME_NAME, pax.getName());
                    values.put(PassengerEntry.COLUMN_NAME_RELATIONSHIP, pax.getRelationship());
                    values.put(PassengerEntry.COLUMN_NAME_TICKET_NUMBER, pax.getTicketNumber());
                    db.insertOrThrow(PassengerEntry.TABLE_NAME, "", values);
                }
            }
        }

        log(String.format("end... inserted %d", insertedRes));
    }

    public void InsertFlights(List<Flight> flights) {
        for (Flight flight : flights)
            InsertFlight(flight);
    }

    public void InsertFlight(Flight flight) {

        log(String.format("starting... %s flight(s)", flight));

        SQLiteDatabase db = getWritableDatabase();
        long inserted = 0;

        if (db != null) {
            ContentValues values = new ContentValues();
            values.clear();
            values.put(FlightEntry.COLUMN_NAME_PNR, flight.getPnr());
            values.put(FlightEntry.COLUMN_NAME_ARRIVAL_DATETIME, flight.getArrival().getTime());
            values.put(FlightEntry.COLUMN_NAME_DEPARTURE_DATETIME, flight.getDeparture().getTime());
            values.put(FlightEntry.COLUMN_NAME_DESTINATION, flight.getDestination());
            values.put(FlightEntry.COLUMN_NAME_FLIGHT_NUMBER, flight.getFlightNumber());
            values.put(FlightEntry.COLUMN_NAME_IS_CHECKED_IN, String.valueOf(flight.isCheckedIn()));
            values.put(FlightEntry.COLUMN_NAME_IS_CHECK_IN_SCHEDULED, String.valueOf(flight.isCheckInScheduled()));
            values.put(FlightEntry.COLUMN_NAME_ORIGIN, flight.getOrigin());
            inserted = db.insertOrThrow(FlightEntry.TABLE_NAME, "", values);
        }
        log(String.format("end... inserted %d", inserted));
    }

    //</editor-fold>

    //<editor-fold desc="Data Deletion">

    public void DeleteReservations() {
        log("starting...");

        SQLiteDatabase db = getWritableDatabase();
        int deleted = 0;

        if (db != null) {
            deleted += db.delete(PassengerEntry.TABLE_NAME, null, null);
            deleted += db.delete(FlightEntry.TABLE_NAME, null, null);
            deleted += db.delete(ReservationEntry.TABLE_NAME, null, null);
        }

        log(String.format("end... deleted %d", deleted));
    }

    public void DeleteReservations(List<Reservation> reservations) {

        for (Reservation res : reservations) {
            DeleteReservation(res.getPnr());
        }
    }

    public void DeleteReservation(String pnr) {

        log("starting...");
        int deleted = 0;

        SQLiteDatabase db = getWritableDatabase();
        String reservationSelection = ReservationEntry.COLUMN_NAME_PNR + " = ?";
        String flightSelection = ReservationEntry.COLUMN_NAME_PNR + " = ?";
        String paxSelection = ReservationEntry.COLUMN_NAME_PNR + " = ?";

        if (db != null) {
            deleted += db.delete(PassengerEntry.TABLE_NAME, paxSelection, new String[]{pnr});
            deleted += db.delete(FlightEntry.TABLE_NAME, flightSelection, new String[]{pnr});
            deleted += db.delete(ReservationEntry.TABLE_NAME, reservationSelection, new String[]{pnr});
        }

        log(String.format("end... deleted %d", deleted));
    }

    public void DeleteFlights(List<Flight> flights) {
        for (Flight flight : flights)
            DeleteFlight(flight);
    }

    public void DeleteFlights(String pnr) {

        log("starting...");

        SQLiteDatabase db = getWritableDatabase();
        int delete = 0;

        String flightSelection = FlightEntry.COLUMN_NAME_PNR + " = ?";
        if (db != null) {
            delete = db.delete(FlightEntry.TABLE_NAME, flightSelection, new String[]{pnr});
        }

        log(String.format("end... deleted %d", delete));
    }

    public void DeleteFlight(Flight flight) {

        log(String.format("starting... flight = %s", flight));

        SQLiteDatabase db = getWritableDatabase();
        int delete = 0;

        String flightSelection = FlightEntry.COLUMN_NAME_PNR + " = ? AND " +
                FlightEntry.COLUMN_NAME_FLIGHT_NUMBER + " = ? AND " +
                FlightEntry.COLUMN_NAME_DEPARTURE_DATETIME + " = ? AND " +
                FlightEntry.COLUMN_NAME_ORIGIN + " = ? ";
        if (db != null) {
            String[] params = new String[]{
                    flight.getPnr(),
                    String.valueOf(flight.getFlightNumber()),
                    String.valueOf(flight.getDeparture().getTime()),
                    flight.getOrigin()
            };
            delete = db.delete(FlightEntry.TABLE_NAME, flightSelection, params);
        }

        log(String.format("end... deleted %d", delete));
    }

    public void DeletePassengers(String pnr) {

        log("starting...");

        SQLiteDatabase db = getWritableDatabase();
        int deleted = 0;

        String paxSelection = PassengerEntry.COLUMN_NAME_PNR + " = ?";
        if (db != null)
            deleted = db.delete(PassengerEntry.TABLE_NAME, paxSelection, new String[]{pnr});

        log(String.format("end... deleted %d", deleted));
    }

    //</editor-fold>

    //<editor-fold desc="Data Updates">

    public void UpdateReservations(List<Reservation> reservations) {
        for (Reservation res : reservations)
            UpdateReservation(res);
    }

    public void UpdateReservation(Reservation res) {

        log("starting...");

        SQLiteDatabase db = getWritableDatabase();
        String reservationSelection = ReservationEntry.COLUMN_NAME_PNR + " = ?";
        int updated = 0;

        if (db != null) {
            ContentValues values = new ContentValues();
            values.clear();
            values.put(ReservationEntry.COLUMN_NAME_DEPARTURE_DATETIME, res.getDeparture().getTime());
            values.put(ReservationEntry.COLUMN_NAME_BOOKING_TYPE, res.getBookingType());

            updated += db.update(ReservationEntry.TABLE_NAME, values, reservationSelection, new String[]{res.getPnr()});

            if (res.getFlights().size() > 0)
                MergeFlights(res.getFlights(), res.getPnr());

            DeletePassengers(res.getPnr());

            for (Passenger pax : res.getPassengers()) {
                values.clear();
                values.put(PassengerEntry.COLUMN_NAME_PNR, res.getPnr());
                values.put(PassengerEntry.COLUMN_NAME_NAME, pax.getName());
                values.put(PassengerEntry.COLUMN_NAME_RELATIONSHIP, pax.getRelationship());
                values.put(PassengerEntry.COLUMN_NAME_TICKET_NUMBER, pax.getTicketNumber());
                db.insertOrThrow(PassengerEntry.TABLE_NAME, "", values);
            }
        }

        log(String.format("end... updated %d", updated));
    }

    public void UpdateFlights(List<Flight> flights) {
        for (Flight flight : flights)
            UpdateFlight(flight);
    }

    public void UpdateFlight(Flight flight) {

        log(String.format("starting... flight = %s, isCheckedIn = %s, isCheckInScheduled = %s",
                flight, flight.isCheckedIn(), flight.isCheckInScheduled()));

        SQLiteDatabase db = getWritableDatabase();
        int updated = 0;

        String flightSelection = FlightEntry.COLUMN_NAME_PNR + " = ? AND " +
                FlightEntry.COLUMN_NAME_FLIGHT_NUMBER + " = ? AND " +
                FlightEntry.COLUMN_NAME_DEPARTURE_DATETIME + " = ? AND " +
                FlightEntry.COLUMN_NAME_ORIGIN + " = ? ";

        if (db != null) {
            String[] params = new String[]{
                    flight.getPnr(),
                    String.valueOf(flight.getFlightNumber()),
                    String.valueOf(flight.getDeparture().getTime()),
                    flight.getOrigin()
            };
            ContentValues values = new ContentValues();
            values.put(FlightEntry.COLUMN_NAME_ARRIVAL_DATETIME, flight.getArrival().getTime());
            values.put(FlightEntry.COLUMN_NAME_DESTINATION, flight.getDestination());
            values.put(FlightEntry.COLUMN_NAME_IS_CHECKED_IN, String.valueOf(flight.isCheckedIn()));
            values.put(FlightEntry.COLUMN_NAME_IS_CHECK_IN_SCHEDULED, String.valueOf(flight.isCheckInScheduled()));

            updated += db.update(FlightEntry.TABLE_NAME, values, flightSelection, params);
        }

        log(String.format("end... updated %d", updated));
    }

    //</editor-fold>

    //<editor-fold desc="Merge methods">

    public void MergeReservations(Collection<Reservation> newRes) {
        log(String.format("starting... %d reservation(s)", newRes.size()));

        List<Reservation> oldRes = SelectReservations();
        List<Reservation> toBeUpdated = new ArrayList<Reservation>();
        List<Reservation> toBeDeleted = new ArrayList<Reservation>();
        List<Reservation> toBeInserted = new ArrayList<Reservation>();

        for (Reservation oldR : oldRes) {
            boolean oldMatch = false;
            for (Reservation newR : newRes) {
                if (oldR.compareTo(newR) == 0) {
                    toBeUpdated.add(newR);
                    oldMatch = true;
                    break;
                }
            }
            if (!oldMatch) toBeDeleted.add(oldR);
        }

        for (Reservation newR : newRes) {
            boolean newMatch = false;
            for (Reservation oldR : oldRes) {
                if (oldR.compareTo(newR) == 0) {
                    newMatch = true;
                    break;
                }
            }
            if (!newMatch) toBeInserted.add(newR);
        }

        if (toBeUpdated.size() > 0) {
            log(String.format("Updating %d reservation(s)", toBeUpdated.size()));
            UpdateReservations(toBeUpdated);
        }

        if (toBeDeleted.size() > 0) {
            log(String.format("Deleting %d reservation(s)", toBeDeleted.size()));
            DeleteReservations(toBeDeleted);
        }

        if (toBeInserted.size() > 0) {
            log(String.format("Inserting %d reservation(s)", toBeInserted.size()));
            InsertReservations(toBeInserted);
        }

        log("end...");
    }

    public void MergeFlights(List<Flight> newFlights, String pnr) {
        log(String.format("starting... flights = %d", newFlights.size()));

        List<Flight> oldFlights = SelectFlights(pnr);
        List<Flight> toBeInserted = new ArrayList<Flight>();
        List<Flight> toBeDeleted = new ArrayList<Flight>();
        List<Flight> toBeUpdated = new ArrayList<Flight>();

        for (Flight oldR : oldFlights) {
            boolean oldMatch = false;
            for (Flight newR : newFlights) {
                if (oldR.compareTo(newR) == 0) {
                    newR.setIsCheckedIn(oldR.isCheckedIn());
                    newR.setIsCheckInScheduled(oldR.isCheckInScheduled());
                    toBeUpdated.add(newR);
                    oldMatch = true;
                    break;
                }
            }
            if (!oldMatch) toBeDeleted.add(oldR);
        }

        for (Flight newR : newFlights) {
            boolean newMatch = false;
            for (Flight oldR : oldFlights) {
                if (oldR.compareTo(newR) == 0) {
                    newMatch = true;
                    break;
                }
            }
            if (!newMatch) toBeInserted.add(newR);
        }

        if (toBeUpdated.size() > 0) {
            log(String.format("Updating %d flight(s)", toBeUpdated.size()));
            UpdateFlights(toBeUpdated);
        }

        if (toBeDeleted.size() > 0) {
            log(String.format("Deleting %d flight(s)", toBeDeleted.size()));
            DeleteFlights(toBeDeleted);
        }

        if (toBeInserted.size() > 0) {
            log(String.format("Inserting %d flight(s)", toBeInserted.size()));
            InsertFlights(toBeInserted);
        }

        log("end...");
    }

    //</editor-fold>
}
